﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;

namespace CommonLibrary.Interface
{
    /// <summary>
    /// 标示SQL语法符号
    /// </summary>
    public class SqlClause
    {
        private string name;
        private string separator;
        private string startFormat;
        private string endFormat;

        public string Name { get { return name; } }

        public string Separator { get { return separator; } }

        public string StartFormat { get { return startFormat; } }

        public string EndFormat { get { return endFormat; } }

        public SqlClause(
            string name,
            string separator = null,
            string start = null,
            string end = null)
        {
            this.name = name;
            this.separator = separator;
            this.startFormat = start;
            this.endFormat = end;
        }
    }

    public class SqlName
    {
        public string SchemaName { get; protected set; }

        public string FullName { get; protected set; }

        public string Owner { get; set; }

        public string Name { get; set; }

        public string Alias { get; set; }

        public SqlName(string name, string alias = null, string owner = null)
        {
            this.Name = name;
            this.Alias = alias;
            this.Owner = owner;

            StringBuilder builder = new StringBuilder();
            if (Owner != null && Owner.Length != 0)
            {
                builder.Append(Owner);
                builder.Append('.');
            }

            builder.Append(Name);
            this.SchemaName = builder.ToString();

            if (Alias != null && Alias.Length != 0)
            {
                builder.Append(" AS ");
                builder.Append(Alias);
            }

            this.FullName = builder.ToString();
        }
        public override string ToString()
        {
            return FullName;
        }

        public static implicit operator SqlName(string name)
        {
            return new SqlName(name);
        }
    }

    public class SqlValue
    {
        public IDbDataParameter Parameter { get; set; }
        public object Value { get; set; }

        public SqlValue(string value) { this.Value = value; }
        public SqlValue(int value) { this.Value = value; }
        public SqlValue(double value) { this.Value = value; }
        public SqlValue(object value) { this.Value = value; }

        public SqlValue(IDbDataParameter parameter)
        {
            this.Parameter = parameter;
        }

        public static implicit operator SqlValue(string value)
        {
            return new SqlValue(value);
        }

        public override string ToString()
        {
            if (this.Parameter != null)
                return this.Parameter.ParameterName;
            if (this.Value != null)
                return this.Value.ToString();

            return "";
        }
    }

    public interface ISqlBuilder
    {
        StringBuilder Buffer { get; }

        void Clear();

        #region Insert

        /// <summary>
        /// INSERT INTO TABLE(COLUMNS...)
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="columns">字段列表</param>
        /// <returns>返回当前对象</returns>
        ISqlBuilder InsertInto(SqlName table, params SqlName[] columns);

        /// <summary>
        /// VALUES(...)
        /// </summary>
        /// <param name="args">the list of values</param>
        /// <returns>current object</returns>
        ISqlBuilder Values(params SqlValue[] args);

        #endregion

        #region Update

        /// <summary>
        /// UPDATE TABLE
        /// </summary>
        /// <param name="table">table's name</param>
        /// <returns>current object</returns>
        ISqlBuilder Update(SqlName table);

        /// <summary>
        /// the clause of update statement
        /// SET column = value, column = value ...
        /// </summary>
        /// <param name="args">the list of 'column = value'</param>
        /// <returns>current object</returns>
        ISqlBuilder Set(params KeyValuePair<SqlName, SqlValue>[] args);

        #endregion

        #region Delete

        /// <summary>
        /// DELETE Table
        /// </summary>
        /// <param name="table">table's name</param>
        /// <returns>current object</returns>
        ISqlBuilder Delete(SqlName table);

        #endregion

        #region Select

        ISqlBuilder Select(params SqlName[] columns);

        ISqlBuilder From(SqlName table);

        /// <summary>
        /// where statement
        /// WHERE condition
        /// </summary>
        /// <param name="condition">the condition of 'select/delete/update' statement</param>
        /// <returns>current object</returns>
        ISqlBuilder Where(string condition);

        ISqlBuilder OrderByAsc(params SqlName[] columns);

        ISqlBuilder OrderByDesc(params SqlName[] columns);

        ISqlBuilder GroupBy(params SqlName[] columns);

        ISqlBuilder Having(string condition);

        ISqlBuilder InnerJoin(SqlName table);

        ISqlBuilder LeftJoin(SqlName table);

        ISqlBuilder FullJoin(SqlName table);

        ISqlBuilder On(string condition);
        #endregion

        ISqlBuilder AppendQuery(ISqlBuilder builder);

        ISqlBuilder AppendQuery(string query);

        string Escape(string input);
    }
}